
from mahbo.information.database.utility.dbinterface import DatabaseInterface

from minings.utility.utility import (RUN,DataPrePackage,)
from copy import deepcopy
import os,numpy,orange,orngMySQL,MySQLdb,xlwt,xlrd,cPickle,string
from xlutils.copy import copy
import psyco
psyco.full() #Speeding Up Python


class SITES(object):
    
    def __init__(self,**kwargs):
        self.Site           = None
        self.Method         = None
        self.Class_X        = None
        self.Class_1        = None
        self.Class_2        = None
        self.Class_3        = None
        self.__dict__.update(kwargs)
        self.built          = True
        
        
class LINKED(object):
    
    def __init__(self,**kwargs):
        self.Capillary      = None
        self.Disease_Status = None
        self.Project        = None
        self.Sex            = None
        self.Age            = None
        self.__dict__.update(kwargs)
        self.built          = True


class GutPackage(DataPrePackage):
    
    def __init__(self,**kwargs):
        #Arbitrary properties
        self.Method         = None
        self.Class_X        = None
        self.Class_2        = None
        self.Class_3        = None
        self.__dict__.update(kwargs)
        DataPrePackage.__init__(self,**kwargs)


class INFO(object):
    
    def __init__(self,**kwargs):
        self.fullname       = ""
        self.dataoption     = 1 #0:Whole classes except self.permremovelist,1:fixclass,2:both,3:all duals
        self.permremovelist = [""]
        self.fixedcls       = ""
        self.matrix         = [ [], [] ]
        self.run            = None
        #Arbitrary properties
        self.Method         = None
        self.Class_X        = None
        self.Class_2        = None
        self.Class_3        = None
        self.Length         = None
        self.__dict__.update(kwargs)


class GUTLHPCR(DatabaseInterface):
    
    def __init__(self,**kwargs):        
        self.mainfullname               = 'gut'
        self.table_gut                  = 'gut'
        self.gut_colon2                 = 'gut_colon2'
        self.gut_colon3                 = 'gut_colon3'
        self.colon_both                 = [self.gut_colon2]
        self.maindataoption             = 1 #0:Whole classes except self.permremovelist,1:fixclass,2:both,3:all duals
        self.mainpermremovelist         = ['Polyp','Diverticulitis','Normal Pouch','Pouchitis','Alcoholic', 'ALD', 'Non ALD']
        self.mainfixedcls               = 'Healthy Control'
        self.mainrun                    = RUN()
        #xcel file for writing
        self.__dict__.update(kwargs)
        DatabaseInterface.__init__(self,self.mainfullname)       
        self.t                          = orngMySQL.Connect('localhost','root','dudesweet',self.mainfullname)
        #Start
        self.getRawdataMySQL()
        
    def getRawdataMySQL(self):
        #Get a list of table names
        self.RA.query("""SELECT * FROM %s""" % (self.RA.tab_L.tablename))
        self.doAll_bool = False #Indicate to do everything anew again or not
        tempraw         = []
        row             = self.RA.cursor.fetchall()
        if row not in (None,()): #I have to make sure table "gut" exists
            self.rawdata    = {}
            #Check whether I have everything ready to construct datapackage
            for i in row:
                dataname    = i[self.RA.tab_L.name]
#                info        = cPickle.loads(i[self.RA.tab_L.info])
#                tempraw.append((dataname,info))   
                tempraw.append((dataname,None))              
                try:
                    #Check if all tables exist or not
                    self.RA.query("""DESC %s""" % (dataname))
                except (MySQLdb.ProgrammingError,),e:
                    if e[0] != 1146:
                        raise MySQLdb.ProgrammingError
                    else:
                        #if only one dataname does not exist
                        self.doAll_bool = True
                        break
        else:
            self.doAll_bool = True
        if self.doAll_bool:
            print 'doAll is True'
            #if signal doAll_bool -> True,do everything again
#            self.doAll()
        else:
            self.rawdata = {}
            for i in tempraw:
                if i[0] not in ('gut_Entire_Colon2','gut_Endo_Ileum','gut_Endo_Colon2','gut_Endo_Sigmoid','gut_Endo_Lumen',): 
                    continue
                print "dataname: %s" % i[0]
#                tempdata            = self.t.query("""SELECT * FROM %s""" % i[0])
#                self.rawdata[i[0]]  = GutPackage(fullname  = i[0]          ,data       = tempdata      ,dataoption = i[1].dataoption   ,permremovelist = i[1].permremovelist   ,
#                                                 fixedcls  = i[1].fixedcls ,matrix     = i[1].matrix   ,run        = i[1].run          ,Method         = i[1].Method           ,
#                                                 Class_X   = i[1].Class_X  ,Class_2    = i[1].Class_2  ,Class_3    = i[1].Class_3      ,Length         = i[1].Length   ) 
##            self.getXcelProperty()
##            self.XCel_Dataset_Orgn()
##            self.XCel_Dataset()
##            self.bookwrite.save(self.xcelfile_write)

    def getXcelProperty(self):
        #Xcel Read properties
        self.orgn_folder                = "C:\\Users\\aonlazio\\Desktop\\BILLION\\PLATFORM\\code\\BL_1\\dataset\\ORIGINAL\\GUT\\LHPCR"
        self.xcel_file                  = "Human-LH-PCR_data_Linked_Modified.xls"
        self.xcel_file_path             = os.path.join(self.orgn_folder,self.xcel_file)
        self.bookread                   = xlrd.open_workbook(self.xcel_file_path)
        self.Clean_Classes_S            = self.bookread.sheet_by_name("Clean classes")
        self.Linked_S                   = self.bookread.sheet_by_name("Linked")
        self.Sample_Sites_S             = self.bookread.sheet_by_name("Sample Sites")
        #Xcel Write properties
        self.xcelfile_write             = "C:\\Users\\aonlazio\\Desktop\\BILLION\\PHD\\dissertation\\documents\\PAPER_1\\PAPER_1_fresh.xls"
        self.col_width                  = 5000
        sheetlist                       = [None,None]
        sheetlist_name                  = ["Dataset_Orgn","Datasets"]
        try:
            self.bookwrite_r            = xlrd.open_workbook(self.xcelfile_write)
            self.bookwrite              = copy(self.bookwrite_r)
        except IOError:
            self.bookwrite_r            = None
            self.bookwrite              = xlwt.Workbook()
            for i in range(len(sheetlist)):
                sheetlist[i] = self.bookwrite.add_sheet(sheetlist_name[i])
        if self.bookwrite_r is not None:
            for i in range(len(sheetlist)):
                try:
                    sheet_temp      = self.bookwrite_r.sheet_by_name(sheetlist_name[i])
                    sheetlist[i]    = self.bookwrite.get_sheet(sheet_temp.number)
                    for j in range(sheet_temp.nrows):
                        for k in range(sheet_temp.ncols):
                            sheetlist[i].row(j).write(k,None)
                except xlrd.XLRDError:
                    sheetlist[i]    = self.bookwrite.add_sheet(sheetlist_name[i])
        self.dset_orgn_sheet,self.dset_sheet = sheetlist
        
    def doAll(self):
        ##########################
        ###Start Gathering Data###
        ##########################
        self.getXcelProperty()
        try:
            self.maindata = self.t.query("""SELECT * FROM %s""" % self.mainfullname)
        except MySQLdb.ProgrammingError,e:
            self.maindata = self.data2orange()
            self.t.write(self.mainfullname,self.maindata,overwrite = True)
        self.modify()
        self.rawdata                    = {}#{'gut_1':DataPackage instance, .. }
        self.keyRank                    = []
        self.getAlldata()
        #Write to xcel files
        self.XCel_Dataset_Orgn()
        self.XCel_Dataset()
        self.bookwrite.save(self.xcelfile_write)
                
    def getAlldata(self):
        #Get Entire_Colon2 with
        #Method     = Endoscopic
        #Class X    = Ileum, Ascending_Colon, Transverse_Colon, Sigmoid with Class_2 and Class_3 has having values != ""
        #             and Lumen, Stool 
        xname               = "Entire_Colon2"
        d_entire_colon2     = self.t.query("""SELECT * FROM %s WHERE ( ( (%s in ("%s","%s","%s","%s")) and (%s!="%s" and %s!="%s") ) or (%s in ("%s")) )
                                                                     and  %s="%s"
                                           """ % (self.table_gut,   "m$Class_X"     ,"Ileum"    ,"Ascending_Colon"  ,"Transverse_Colon" ,"Sigmoid"      ,
                                                                    "m$Class_2"     ,""         ,"m$Class_3"        ,""                 ,"m$Class_X"    ,
                                                                    "Lumen"         ,"m$Method" ,"Endoscopic"       ))
        self.rawdata[xname] = DataPackage(fullname          = xname                     ,data       = d_entire_colon2       ,dataoption = self.maindataoption   ,
                                          permremovelist    = self.mainpermremovelist   ,fixedcls   = self.mainfixedcls     ,run        = self.mainrun          ,
                                          Method            = "Endoscopic"              ,Class_X    = None                  ,Class_2    = None                  ,
                                          Class_3           = None                      ,Length     = len(d_entire_colon2)     )
        self.keyRank.append(xname)
        
        #Get Endo_Ileum with
        #Method     = Endoscopic
        #Class_X    = Ileum and Class_2 and Class_3 has having values != ""
        xname               = "Endo_Ileum"
        d_endo_ileum        = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s="%s" and %s!="%s" and %s!="%s")
                                           """ % (self.table_gut,   "m$Class_X"     ,"Ileum"    ,"m$Method"         ,"Endoscopic"   ,
                                                                    "m$Class_2"     ,""         ,"m$Class_3"        ,""     ) )
        #Find method and class_x
        self.rawdata[xname] = DataPackage(fullname          = xname                     ,data       = d_endo_ileum          ,dataoption = self.maindataoption   ,
                                          permremovelist    = self.mainpermremovelist   ,fixedcls   = self.mainfixedcls     ,run        = self.mainrun          ,
                                          Method            = "Endoscopic"              ,Class_X    = "Ileum"               ,Class_2    = None                  ,
                                          Class_3           = None                      ,Length     = len(d_endo_ileum)     )
        self.keyRank.append(xname)
                
        #Get Endo_Colon2 (Ascending_Colon + Transverse_Colon) with Class_2 and Class_3 has having values != ""
        xname               = "Endo_Colon2"
        d_endo_colon2       = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s","%s")) and (%s="%s" and %s!="%s" and %s!="%s")
                                           """ % (self.table_gut,   "m$Class_X"     ,"Ascending_Colon"  ,"Transverse_Colon"     ,"m$Method"     ,"Endoscopic"   ,
                                                                    "m$Class_2"     ,""                 ,"m$Class_3"            ,""     ) )
        #Find method and class_x
        self.rawdata[xname] = DataPackage(fullname          = xname                     ,data       = d_endo_colon2         ,dataoption = self.maindataoption   ,
                                          permremovelist    = self.mainpermremovelist   ,fixedcls   = self.mainfixedcls     ,run        = self.mainrun          ,
                                          Method            = "Endoscopic"              ,Class_X    = None                  ,Class_2    = None                  ,
                                          Class_3           = None                      ,Length     = len(d_endo_colon2)     )
        self.keyRank.append(xname)

        #Get Endo_Sigmoid with Class_2 and Class_3 has having values != ""
        xname               = "Endo_Sigmoid"
        d_endo_sigmoid      = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s="%s" and %s!="%s" and %s!="%s")
                                           """ % (self.table_gut,   "m$Class_X"     ,"Sigmoid"      ,"m$Method"     ,"Endoscopic"   ,
                                                                    "m$Class_2"     ,""             ,"m$Class_3"    ,""     ) )
        #Find method and class_x
        self.rawdata[xname] = DataPackage(fullname          = xname                     ,data       = d_endo_sigmoid        ,dataoption = self.maindataoption   ,
                                          permremovelist    = self.mainpermremovelist   ,fixedcls   = self.mainfixedcls     ,run        = self.mainrun          ,
                                          Method            = "Endoscopic"              ,Class_X    = None                  ,Class_2    = None                  ,
                                          Class_3           = None                      ,Length     = len(d_endo_sigmoid)   )
        self.keyRank.append(xname)

        #Get Endo_Lumen
        xname                   = "Endo_Lumen"
        d_endo_lumen            = self.t.query("""SELECT * FROM %s WHERE %s="%s" and %s="%s"
                                               """ % (self.table_gut,    "m$Method"      ,"Endoscopic"       ,"m$Class_X"    ,"Lumen" ) )
        self.rawdata[xname]     = DataPackage(fullname          = xname                     ,data       = d_endo_lumen          ,dataoption = self.maindataoption   ,
                                              permremovelist    = self.mainpermremovelist   ,fixedcls   = self.mainfixedcls     ,run        = self.mainrun          ,
                                              Method            = "Endoscopic"              ,Class_X    = "Lumen"               ,Class_2    = None                  ,
                                              Class_3           = None                      ,Length     = len(d_endo_lumen)     )
        self.keyRank.append(xname)
        
        #Get Endo_Ileum_Lumen | Healthy Control, Crohn, Ulcer from gut_colon2 table
        xname                       = "Endo_Ileum_Lumen"
        d_endo_ileum_lumen          = self.t.query("""SELECT * FROM %s WHERE ( (%s in ("%s","%s","%s") and (%s!="%s" and %s!="%s")) or (%s in ("%s","%s","%s")) )
                                                                              and %s="%s"
                                                   """ % (self.gut_colon2,  "c$Disease"     ,"Healthy Control_Ileum"    ,"Crohn_Ileum"      ,"Ulcerative Colitis_Ileum"     ,
                                                                            "m$Class_2"     ,""                         ,"m$Class_3"        ,""                             ,
                                                                            "c$Disease"     ,"Healthy Control_Lumen"    ,"Crohn_Lumen"      ,"Ulcerative Colitis_Lumen"     ,
                                                                            "m$Method"      ,"Endoscopic"       ))
        self.rawdata[xname]         = DataPackage(fullname    = xname             ,data       = d_endo_ileum_lumen          ,dataoption = 4         ,
                                                  matrix      = [ ["Crohn_Ileum","Ulcerative Colitis_Ileum","Healthy Control_Ileum"],
                                                                  ["Crohn_Lumen","Ulcerative Colitis_Lumen","Healthy Control_Lumen"] ]              ,
                                                  run         = self.mainrun      ,Method     = "Endoscopic"                ,Class_X    = None      ,
                                                  Class_2     = None              ,Class_3    = None                        ,Length     = len(d_endo_ileum_lumen) )                                              
        self.keyRank.append(xname)

        #Get Endo_Colon2_Lumen | Healthy Control, Crohn, Ulcer from gut_colon2 table
        xname                       = "Endo_Colon2_Lumen"
        d_endo_colon2_lumen         = self.t.query("""SELECT * FROM %s WHERE ( (%s in ("%s","%s","%s") and (%s!="%s" and %s!="%s")) or (%s in ("%s","%s","%s")) )
                                                                             and %s="%s"
                                                   """ % (self.gut_colon2,  "c$Disease"     ,"Healthy Control_Colon"    ,"Crohn_Colon"      ,"Ulcerative Colitis_Colon"     ,
                                                                            "m$Class_2"     ,""                         ,"m$Class_3"        ,""                             ,
                                                                            "c$Disease"     ,"Healthy Control_Lumen"    ,"Crohn_Lumen"      ,"Ulcerative Colitis_Lumen"     ,
                                                                            "m$Method"      ,"Endoscopic"       ))
        self.rawdata[xname]         = DataPackage(fullname    = xname             ,data       = d_endo_colon2_lumen         ,dataoption = 4         ,
                                                  matrix      = [ ["Crohn_Colon","Ulcerative Colitis_Colon","Healthy Control_Colon"],
                                                                  ["Crohn_Lumen","Ulcerative Colitis_Lumen","Healthy Control_Lumen"] ]              ,
                                                  run         = self.mainrun      ,Method     = "Endoscopic"                ,Class_X    = None      ,
                                                  Class_2     = None              ,Class_3    = None                        ,Length     = len(d_endo_colon2_lumen) )
        self.keyRank.append(xname)

        #Get Endo_Sigmoid_Lumen | Healthy Control, Crohn, Ulcer from gut_colon2 table
        xname                       = "Endo_Sigmoid_Lumen"
        d_endo_sigmoid_lumen        = self.t.query("""SELECT * FROM %s WHERE ( (%s in ("%s","%s","%s") and (%s!="%s" and %s!="%s")) or (%s in ("%s","%s","%s")) )
                                                                             and %s="%s"
                                                   """ % (self.gut_colon2,  "c$Disease"     ,"Healthy Control_Sigmoid"  ,"Crohn_Sigmoid"    ,"Ulcerative Colitis_Sigmoid"   ,
                                                                            "m$Class_2"     ,""                         ,"m$Class_3"        ,""                             ,
                                                                            "c$Disease"     ,"Healthy Control_Lumen"    ,"Crohn_Lumen"      ,"Ulcerative Colitis_Lumen"     ,
                                                                            "m$Method"      ,"Endoscopic"       ))
        self.rawdata[xname]         = DataPackage(fullname    = xname             ,data       = d_endo_sigmoid_lumen        ,dataoption = 4         ,
                                                  matrix      = [ ["Crohn_Sigmoid","Ulcerative Colitis_Sigmoid","Healthy Control_Sigmoid"],
                                                                  ["Crohn_Lumen","Ulcerative Colitis_Lumen","Healthy Control_Lumen"] ]              ,
                                                  run         = self.mainrun      ,Method     = "Endoscopic"                ,Class_X    = None      ,
                                                  Class_2     = None              ,Class_3    = None                        ,Length     = len(d_endo_sigmoid_lumen) )
        self.keyRank.append(xname)

        #Get Endo_Healthy_Colon2 | Healthy Control of Ileum vs Colon vs Sigmoid vs Lumen vs Stool
        xname                       = "Endo_Healthy_Colon2"
        d_endo_healthy_colon2       = self.t.query("""SELECT * FROM %s WHERE ( (%s in ("%s","%s","%s") and (%s!="%s" and %s!="%s")) or (%s in ("%s","%s")) )
                                                                             and %s="%s"
                                                   """ % (self.gut_colon2,  "c$Disease"     ,"Healthy Control_Ileum"    ,"Healthy Control_Colon"    ,"Healthy Control_Sigmoid"  , 
                                                                            "m$Class_2"     ,""                         ,"m$Class_3"                ,""                         ,  
                                                                            "c$Disease"     ,"Healthy Control_Lumen"    ,"Healthy Control_Stool"    ,
                                                                            "m$Method"      ,"Endoscopic"       ))
        self.rawdata[xname]         = DataPackage(fullname    = xname             ,data       = d_endo_healthy_colon2       ,dataoption = 3         ,
                                                  run         = self.mainrun      ,Method     = "Endoscopic"                ,Class_X    = None      ,
                                                  Class_2     = None              ,Class_3    = None                        ,Length     = len(d_endo_healthy_colon2) )
        self.keyRank.append(xname)
        #Record Data
        for i in self.keyRank:
            if i != self.mainfullname:
                fullname = '%s_%s' % (self.mainfullname,i)
            else:
                fullname = self.mainfullname
            self.rawdata[i].data  = utility.DatasetTools.finalBrush(self.rawdata[i].data)
            try:
                self.t.write(fullname,self.rawdata[i].data,overwrite = True)
            except (MySQLdb.OperationalError,):
                pass
            info_obj    = INFO(fullname = self.rawdata[i].fullname  ,dataoption = self.rawdata[i].dataoption    ,permremovelist = self.rawdata[i].permremovelist    ,
                               fixedcls = self.rawdata[i].fixedcls  ,matrix     = self.rawdata[i].matrix        ,run            = self.rawdata[i].run               ,
                               Method   = self.rawdata[i].Method    ,
                               Class_X  = self.rawdata[i].Class_X   ,Class_2    = self.rawdata[i].Class_2       ,Class_3        = self.rawdata[i].Class_3           ,
                               Length   = self.rawdata[i].Length )
            info_string = MySQLdb.escape_string(cPickle.dumps(info_obj,2))
            self.R.cursor.execute("""insert into %s values  ("%s","%s")
                                  """ % ( self.R.tab_L.tname, fullname, info_string ) )
            
    def XCel_Dataset_Orgn(self):
        data = self.t.query("""SELECT * FROM %s""" % self.mainfullname)
        data = utility.DatasetTools.finalBrush(data)
        name = self.mainfullname
        for i in range(3):
            self.dset_orgn_sheet.col(i).width = self.col_width
        self.cur_row = 0
        self.dset_orgn_sheet.row(self.ri()).write(0,"Dataset Name")
        self.dset_orgn_sheet.row(self.ri()).write(0,"Total Instances")
        self.dset_orgn_sheet.row(self.ri()).write(0,"Total Features")
        self.dset_orgn_sheet.row(self.ri()).write(0,"Class Distribution")
        self.cur_row = 0
        self.dset_orgn_sheet.row(self.ri()).write(2,self.mainfullname.upper())
        self.dset_orgn_sheet.row(self.ri()).write(2,len(data))
        self.dset_orgn_sheet.row(self.ri()).write(2,len(data.domain.attributes))
        cur_row = self.cur_row
        for cls,number in zip(data.domain.classVar.values,
                              orange.Distribution(data.domain.classVar,data)):
            self.dset_orgn_sheet.row(cur_row).write(1,cls)
            self.dset_orgn_sheet.row(cur_row).write(2,int(number))
            cur_row += 1
            
    def XCel_Dataset(self):
        keyRank = []
        rawdata = {}
        self.R.cursor.execute("""select * from %s""" % (self.R.tab_L.tname) )
        row     = self.R.cursor.fetchall()
        for i in row:
            dataname                = i[self.R.tab_L.name]
            info                    = cPickle.loads(i[self.R.tab_L.info])            
            tempdata                = self.t.query("""SELECT * FROM %s""" % dataname)
            rawdata[dataname]       = DataPackage(fullname  = dataname          ,data       = tempdata      ,dataoption = info.dataoption   ,permremovelist = info.permremovelist   ,
                                                  fixedcls  = info.fixedcls     ,matrix     = info.matrix   ,run        = info.run          ,Method         = info.Method           ,
                                                  Class_X   = info.Class_X      ,Class_2    = info.Class_2  ,Class_3    = info.Class_3      ,Length         = info.Length           ,
                                                  getPackage = False  )
            rawdata[dataname].data  = utility.DatasetTools.finalBrush(rawdata[dataname].data)
            keyRank.append(dataname)
        for i in range(20):
            self.dset_sheet.col(i).width = self.col_width
        focusedClass    = ["Crohn","Ulcerative Colitis","Healthy Control"]
        #Write Dataset sheet for Entire_X,Entire_Colon2,Entire_Colon4,Endo_Ileum,Endo_Colon2,Endo_Colon4,Endo_Lumen,Endo_Stool,
        d_list          = ["gut_Entire_Colon2","gut_Endo_Ileum","gut_Endo_Colon2","gut_Endo_Sigmoid","gut_Endo_Lumen"]
        self.cur_row    = 0
        row_encore      = self.cur_row
        self.dset_sheet.row(self.ri()).write(0,"Dataset Name")
        self.dset_sheet.row(self.ri()).write(0,"Total Instances")
        self.dset_sheet.row(self.ri()).write(0,"Total Features")
        self.dset_sheet.row(self.ri()).write(0,"Class Distribution")
        self.cur_row    -= 1
        self.dset_sheet.row(self.ri()).write(1,"Crohn")
        self.dset_sheet.row(self.ri()).write(1,"Ulcerative Colitis")
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control")
        cur_col         = 1
        for i in keyRank:
            if i not in d_list: continue
            datalen         = 0
            cur_col         += 1
            self.cur_row    = row_encore
            data            = rawdata[i].data
            b               = {}
            for cls,number in zip(data.domain.classVar.values,
                                  orange.Distribution(data.domain.classVar,data)):
                if cls in focusedClass:
                    b[cls]  =   number
                    datalen +=  number
            self.dset_sheet.row(self.ri()).write(cur_col,i)
            self.dset_sheet.row(self.ri()).write(cur_col,datalen)
            self.dset_sheet.row(self.ri()).write(cur_col,len(data.domain.attributes))
            self.dset_sheet.row(self.ri()).write(cur_col,b["Crohn"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Ulcerative Colitis"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control"])

        self.cur_row    += 2
        row_encore      = self.cur_row
        #Write Dataset_Special sheet Endo_Ileum_Lumen,Endo_Colon2_Lumen,Endo_Colon4_Lumen
        d_list_spc      = ["gut_Endo_Ileum_Lumen","gut_Endo_Colon2_Lumen","gut_Endo_Sigmoid_Lumen"]
        self.dset_sheet.row(self.ri()).write(0,"Dataset Name")
        self.dset_sheet.row(self.ri()).write(0,"Total Instances")
        self.dset_sheet.row(self.ri()).write(0,"Total Features")
        self.dset_sheet.row(self.ri()).write(0,"Disease Distribution")
        self.cur_row    -= 1
        self.dset_sheet.row(self.ri()).write(1,"Crohn_Site")
        self.dset_sheet.row(self.ri()).write(1,"Crohn_Lumen")
        self.dset_sheet.row(self.ri()).write(1,"Ulcerative Colitis_Site")
        self.dset_sheet.row(self.ri()).write(1,"Ulcerative Colitis_Lumen")
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control_Site")
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control_Lumen")
        cur_col         = 1
        for i in keyRank:
            if i not in d_list_spc: continue
            cur_col         += 1
            self.cur_row    = row_encore
            data            = rawdata[i].data
            b               = {}
            for cls,number in zip(data.domain.classVar.values,
                                  orange.Distribution(data.domain.classVar,data)):
                g = cls.split("_")
                if g[1] != "Lumen":
                    clsname = "%s_Site" % (g[0])
                else:
                    clsname = cls
                b[clsname] = number
            self.dset_sheet.row(self.ri()).write(cur_col,i)
            self.dset_sheet.row(self.ri()).write(cur_col,len(data))
            self.dset_sheet.row(self.ri()).write(cur_col,len(data.domain.attributes))
            self.dset_sheet.row(self.ri()).write(cur_col,b["Crohn_Site"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Crohn_Lumen"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Ulcerative Colitis_Site"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Ulcerative Colitis_Lumen"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control_Site"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control_Lumen"])

        self.cur_row    += 2
        row_encore      = self.cur_row
        #Write Dataset_Special sheet Endo_Healthy_Colon2
        d_list_healthy  = ["gut_Endo_Healthy_Colon2"]
        self.dset_sheet.row(self.ri()).write(0,"Dataset Name")
        self.dset_sheet.row(self.ri()).write(0,"Total Instances")
        self.dset_sheet.row(self.ri()).write(0,"Total Features")
        self.dset_sheet.row(self.ri()).write(0,"Disease Distribution")
        self.cur_row    -= 1
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control_Ileum")
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control_Colon")
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control_Sigmoid")
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control_Lumen")
        self.dset_sheet.row(self.ri()).write(1,"Healthy Control_Stool")
        cur_col         = 1
        for i in keyRank:
            if i not in d_list_healthy: continue
            cur_col         += 1
            self.cur_row    = row_encore
            data            = rawdata[i].data
            b               = {}
            for cls,number in zip(data.domain.classVar.values,
                                  orange.Distribution(data.domain.classVar,data)):
                b[cls] = number
            self.dset_sheet.row(self.ri()).write(cur_col,i)
            self.dset_sheet.row(self.ri()).write(cur_col,len(data))
            self.dset_sheet.row(self.ri()).write(cur_col,len(data.domain.attributes))
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control_Ileum"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control_Colon"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control_Sigmoid"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control_Lumen"])
            self.dset_sheet.row(self.ri()).write(cur_col,b["Healthy Control_Stool"])
            
    def getInf(self):
        self.capil_class    = {} #Match capil to class {'4419':'Crohns', .. }
        self.Linked_OBJ     = {} #{Capil:LINKED object, ..}
        self.Sites_OBJ      = {}
        self.class2index    = {} #{"Crohns":0,"Crohns-ileitis":1,"Diverticulitis":2,"Polyp":3,"Pouchitis":4,"Ulcerative colitis":5,"Healthy Control":6}
        self.classList      = [] #["Crohns","Crohns-ileitis","Diverticulitis","Polyp","Pouchitis","Ulcerative colitis","Healthy Control"]
        tempclass2index     = {}
        cntClass            = 0
        for row in range(self.Clean_Classes_S.nrows):
            if row == 0: 
                continue
            value = []
            for col in range(self.Clean_Classes_S.ncols):
                value.append(str(self.Clean_Classes_S.cell(row,col).value))
            if value[5] == 'None':
                continue
            self.capil_class[str(int(float(value[0])))]     = value[5]
            try:
                tempclass2index[value[5]].append(cntClass)
            except KeyError:
                tempclass2index[value[5]]       = []
                self.classList.append(value[5])
                self.class2index[value[5]]      = cntClass
                cntClass                        += 1
        for row in range(self.Sample_Sites_S.nrows):
            if row == 0: continue
            value = []
            for col in range(self.Sample_Sites_S.ncols):
                value.append(str(self.Sample_Sites_S.cell(row,col).value))
            try:
                float(value[0])
                site = str(int(float(value[0])))
            except ValueError:
                continue
            for j in range(len(value)):
                try:
                    self.Sites_OBJ[site].built
                except KeyError:
                    self.Sites_OBJ[site] = SITES(Site = site)
                if j == 1:
                    self.Sites_OBJ[site].Method     = value[j]
                if j == 2:
                    self.Sites_OBJ[site].Class_X    = value[j]
                if j == 3:
                    self.Sites_OBJ[site].Class_1    = value[j]
                if j == 4:
                    self.Sites_OBJ[site].Class_2    = value[j]
                if j == 5:
                    self.Sites_OBJ[site].Class_3    = value[j]
        capil_list = self.capil_class.keys()
        for row in range(self.Linked_S.nrows):
            if row == 0: continue
            value = []
            for col in range(self.Linked_S.ncols):
                value.append(str(self.Linked_S.cell(row,col).value))
            try:
                capil   = str(int(float(value[0])))
            except (ValueError,):
                continue
            try:
                capil_list.index(capil)
            except ValueError:
                continue
            for j in range(len(value)):
                try:
                    self.Linked_OBJ[capil].built
                except KeyError:
                    self.Linked_OBJ[capil]                  = LINKED(Capillary = capil)
                if j == 5:
                    self.Linked_OBJ[capil].Project          = value[j]
                if j == 25:
                    self.Linked_OBJ[capil].Disease_Status   = value[j]
                if j == 33:
                    self.Linked_OBJ[capil].Age              = value[j]
                if j == 34:
                    self.Linked_OBJ[capil].Sex              = value[j]
                    
    def data2orange(self):
        self.getInf() #Get Class Info
        self.features               = []
        self.meta_capillary         = [] #Set up the empty list of capillary
        self.meta_sample_name       = []
        self.meta_sample_site       = []
        self.meta_method            = []
        self.meta_class_X           = []
        self.meta_class_1           = []
        self.meta_class_2           = []
        self.meta_class_3           = []
        self.meta_project           = []
        self.meta_disease_status    = []
        self.meta_sex               = []
        self.meta_age               = []
        self.sample                 = [] #The entire dataset
        for row in range(self.Clean_Classes_S.nrows):
            value = []
            for col in range(self.Clean_Classes_S.ncols):
                value.append(str(self.Clean_Classes_S.cell(row,col).value))
            local   = []
            if row != 0: #By Pass Class None
                if value[5] == "None":
                    continue
                else:
                    label = value[5]
            for j in range(len(value)):
                if row == 0:
                    if j >= 6:
                        otu = "%.2f" % float(value[j])
                        u1  = otu.replace('.','_')
                        ft  = orange.FloatVariable(u1)
                        self.features.append(ft)
                else:
                    if j == 0:
                        capil_value = str(int(float(value[j])))
                        self.meta_capillary.append(capil_value)
                        try:
                            self.Linked_OBJ[capil_value].built
                            self.meta_disease_status.append(self.Linked_OBJ[capil_value].Disease_Status)
                            self.meta_project.append(self.Linked_OBJ[capil_value].Project)
                            self.meta_sex.append(self.Linked_OBJ[capil_value].Sex)
                            self.meta_age.append(self.Linked_OBJ[capil_value].Age)                            
                        except KeyError:
                            self.meta_disease_status.append("")
                            self.meta_project.append("")
                            self.meta_sex.append("")
                            self.meta_age.append("")                            
                    if j == 1:
                        self.meta_sample_name.append(value[j])
                    if j == 3:
                        site_value = str(int(float(value[j])))
                        self.meta_sample_site.append(site_value)
                        try:
                            self.Sites_OBJ[site_value].built
                            self.meta_method.append(self.Sites_OBJ[site_value].Method)
                            self.meta_class_X.append(self.Sites_OBJ[site_value].Class_X)
                            self.meta_class_1.append(self.Sites_OBJ[site_value].Class_1)
                            self.meta_class_2.append(self.Sites_OBJ[site_value].Class_2)
                            self.meta_class_3.append(self.Sites_OBJ[site_value].Class_3)
                        except KeyError:
                            self.meta_method.append("")
                            self.meta_class_X.append("")
                            self.meta_class_1.append("")
                            self.meta_class_2.append("")
                            self.meta_class_3.append("")
                    if j >= 6:
                        local.append(float(value[j]))
                    if j == len(value) - 1:
                        local.append(self.class2index[label])
            if local != []:
                self.sample.append(deepcopy(local))
        #Construct orange class
        newClassVar         = orange.EnumVariable("Disease",values = self.classList)
        self.sample_d       = deepcopy(numpy.array(self.sample,'d'))
        finalDomain         = orange.Domain(self.features,newClassVar)
        data                = orange.ExampleTable(finalDomain,self.sample_d)
        capillary_id        = orange.newmetaid()
        sample_name_id      = orange.newmetaid()
        sample_site_id      = orange.newmetaid()
        method_id           = orange.newmetaid()
        class_X_id          = orange.newmetaid()
        class_1_id          = orange.newmetaid()
        class_2_id          = orange.newmetaid()
        class_3_id          = orange.newmetaid()
        project_id          = orange.newmetaid()
        disease_status_id   = orange.newmetaid()
        sex_id              = orange.newmetaid()
        age_id              = orange.newmetaid()
        data.domain.addmeta(capillary_id,orange.StringVariable("Capillary"))
        data.domain.addmeta(sample_name_id,orange.StringVariable("Sample_Name"))
        data.domain.addmeta(sample_site_id,orange.StringVariable("Sample_Site"))
        data.domain.addmeta(method_id,orange.StringVariable("Method"))
        data.domain.addmeta(class_X_id,orange.StringVariable("Class_X"))
        data.domain.addmeta(class_1_id,orange.StringVariable("Class_1"))
        data.domain.addmeta(class_2_id,orange.StringVariable("Class_2"))
        data.domain.addmeta(class_3_id,orange.StringVariable("Class_3"))
        data.domain.addmeta(disease_status_id,orange.StringVariable("Disease_Status"))
        data.domain.addmeta(project_id,orange.StringVariable("Project"))
        data.domain.addmeta(sex_id,orange.StringVariable("Sex"))
        data.domain.addmeta(age_id,orange.StringVariable("Age"))
        for i in range(len(data)):
            data[i]["Capillary"]        = self.meta_capillary[i]
            data[i]["Sample_Name"]      = self.meta_sample_name[i]
            data[i]["Sample_Site"]      = self.meta_sample_site[i]
            data[i]["Method"]           = self.meta_method[i]
            data[i]["Class_X"]          = self.meta_class_X[i]
            data[i]["Class_1"]          = self.meta_class_1[i]
            data[i]["Class_2"]          = self.meta_class_2[i]
            data[i]["Class_3"]          = self.meta_class_3[i]
            data[i]["Disease_Status"]   = self.meta_disease_status[i]
            data[i]["Project"]          = self.meta_project[i]
            data[i]["Sex"]              = self.meta_sex[i]            
            data[i]["Age"]              = self.meta_age[i]
        data = utility.DatasetTools.finalBrush(data)
        return data
    
    def ci(self):
        self.cur_col += 1
        return self.cur_col - 1
    
    def ri(self):
        self.cur_row += 1
        return self.cur_row - 1

